﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WEPMlibrary_Model;
using WEPMconnection_DAL;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace WEPMconnection_DAL
{
    public class Project_DAL
    {
        Conntion Con = new Conntion();
        /// <summary>
        /// 数据库中的记录数               
        /// </summary>
        /// <returns></returns>
        public int getCount()
        {
            int resault = 0;
            using (SqlConnection conn = Con.Con())
            {
                try
                {
                    conn.Open();
                    StringBuilder str = new StringBuilder();
                    str.AppendLine("select COUNT(*) from [Project]");
                    SqlCommand command = new SqlCommand(str.ToString(), conn);
                    resault = Convert.ToInt32(command.ExecuteScalar());
                }
                catch (Exception)
                {

                    throw;
                }
                finally
                {
                    conn.Close();
                }

            }
            return resault;
        }
        /// <summary>
        /// 项目显示
        /// </summary>
        /// <returns></returns>
        public List<Project> ProjectShow(int index, int size)
        {
            var pageIndex = (index - 1) * size + 1;
            var pageSize = index * size;
            SqlConnection conn = Con.Con();
            List<Project> plist = new List<Project>();
            try
            {
                conn.Open();
                string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.Id) r, ui.Name,p.Id, p.ProjectName, p.YesAccredit, p.DeclareTime, p.Area,       p.ProjectState,p2.ProjectTType  FROM UserInfor AS ui INNER JOIN Project AS p on ui.Id=p.Uid INNER JOIN Projectsypes AS p2 ON p.Tid=p2.id) d WHERE r BETWEEN "+pageIndex+" AND "+pageSize+"";
                SqlCommand com = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                da.Fill(dt);
                foreach (DataRow item in dt.Rows)
                {
                    Project p = new Project();
                    p.Area = item["Area"].ToString();
                    p.DeclareTime = Convert.ToDateTime(item["DeclareTime"]).ToString("hh:mm");
                    p.Id = Convert.ToInt32(item["Id"]);
                    
                    p.ProjectName = item["ProjectName"].ToString();
                    p.ProjectState = item["ProjectState"].ToString();
                    p.ProjectType = item["ProjectTType"].ToString();
                    p.YesAccredit = item["YesAccredit"].ToString();
                    p.Name = item["Name"].ToString();
                    plist.Add(p);
                }
            }
            catch
            {
                throw;

            }
            finally
            {
                conn.Close();
            }
            return plist;
        }
        /// <summary>
        /// 新建项目
        /// </summary>
        /// <returns></returns>
        public int ProjectAdd(Project p)
        {
            int retlist;
            SqlConnection conn = Con.Con();
            try
            {
                conn.Open();
                string sql = "insert into Project values('" + p.Tid + "','" + p.ProjectName + "','" + p.YesAccredit + "','" + p.DeclareTime + "','" + p.Area + "','" + p.Uid + "','" + p.ProjectState + "')";
                SqlCommand com = new SqlCommand(sql, conn);
                retlist = com.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return retlist;

        }
        /// <summary>
        /// 添加成员
        /// </summary>
        /// <returns></returns>
        public int MemberAdd(Member m)
        {
            int retlist;
            SqlConnection conn = Con.Con();
            try
            {
                conn.Open();
                string sql = "SELECT m.Id,m.MemberTyep,m.Uid,m.Projectid FROM Member AS m";
                SqlCommand com = new SqlCommand(sql, conn);
                retlist = com.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return retlist;
        }
        /// <summary>
        /// 成员下拉框
        /// </summary>
        /// <returns></returns>
        public List<Member> MemberXiaLa()
        {
            SqlConnection conn = Con.Con();
            List<Member> mlist = new List<Member>();
            try
            {
                
                conn.Open();
                string sql = "SELECT m.Id, mt.MemberTyep,ui.Name,p.ProjectNameFROM MemberTyeps AS mt JOIN Member AS m ON mt.id=m.MemberTypeId JOIN UserInfor AS ui ON m.Uid=ui.Id JOINProject AS p ON m.Projectid=p.Id";
                SqlCommand com = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
               
                foreach (DataRow item in dt.Rows)
                {
                    Member m = new Member();
                    m.Id = Convert.ToInt32(item["Id"]);
                    m.MemberTyep = item["MemberTyep"].ToString();
                    m.Name = item["Name"].ToString();
                    m.ProjectName = item["ProjectName"].ToString();
                    mlist.Add(m);
                }
            }
            catch
            {
                throw;
            }
            finally {
                conn.Close();
            }
            return mlist;
        }
        /// <summary>
        /// 新建项目显示
        /// </summary>
        /// <param name="index"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public List<Project> MemberAdd()
        {
            SqlConnection conn = Con.Con();
            List<Project> plist = new List<Project>();
            try
            {
                conn.Open();
                string sql = "SELECT p.Id,p.ProjectName FROM Project AS p WHERE p.ProjectState=1";
                SqlCommand com = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                da.Fill(dt);
                foreach (DataRow item in dt.Rows)
                {
                    Project p = new Project();
                    p.Id = Convert.ToInt32(item["Id"]);
                    p.ProjectName = item["ProjectName"].ToString();
                    plist.Add(p);
                }
            }
            catch
            {
                throw;

            }
            finally
            {
                conn.Close();
            }
            return plist;
        }
        /// <summary>
        /// 用户显示供项目负责人分配职责
        /// </summary>
        /// <returns></returns>
        public List<UserInfor> UserInforShow()
        {
            SqlConnection conn = Con.Con();
            List<UserInfor> plist = new List<UserInfor>();
            try
            {
                conn.Open();
                string sql = "SELECT ui.Id, ui.Name FROM UserInfor AS ui";
                SqlCommand com = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataTable dt = new DataTable();
                da.Fill(dt);
                foreach (DataRow item in dt.Rows)
                {
                    UserInfor p = new UserInfor();
                    p.Id = Convert.ToInt32(item["Id"]);
                    p.Name = item["Name"].ToString();
                    plist.Add(p);
                }
            }
            catch
            {
                throw;

            }
            finally
            {
                conn.Close();
            }
            return plist;
        }
        /// <summary>
        /// 添加项目负责人
        /// </summary>
        /// <returns></returns>
        public int AddProjectmen(string Types,int Uid,int Pid)
        {
            SqlConnection conn = Con.Con();
            int result = 0;
            try
            {
                conn.Open();
                StringBuilder str = new StringBuilder();
                str.AppendLine(" INSERT INTO Member ( MemberTyep,Uid,Projectid) VALUES('" + Types + "'," + Uid + "," + Pid + ")");
                SqlCommand com = new SqlCommand(str.ToString(), conn);
                result = com.ExecuteNonQuery();
            }
            catch
            {
                throw;

            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        
    }
}
